import sqlite3 from 'sqlite3'
import path from 'path'
import { app } from 'electron'

export let db: any = null

// 初始化本地数据库sqlite3
export function initSqlite() {
  if (db) return db
  // 数据库位置：C:\Users\电脑用户名\AppData\Roaming\项目名称\database.db
  let DB_PATH = path.join(app.getPath('userData'), '/database.db')
  // 创建数据库
  db = new sqlite3.Database(DB_PATH, (err: any) => {
    if (!err) {
      console.log('sqlite连接成功, 数据库文件位置：' + DB_PATH)
    } else {
      console.log('sqlite连接失败', err)
    }
  })
  return db
}

// 获取数据库实例
export function getDB() {
  if (!db) {
    db = initSqlite()
  }
  return db
}

// 关闭数据库连接
export function close() {
  db &&
    db.close((err: any) => {
      if (err) {
        console.error('sqlite数据库关闭失败:', err.message)
      } else {
        console.log('sqlite数据库已关闭')
        db = null
      }
    })
}

export const create = () => {
  const sql = `
  CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chatId TEXT NOT NULL,
    msgFrom JSON NOT NULL,
    msgTo JSON NOT NULL,
    msgType TEXT NOT NULL,
    message TEXT NOT NULL,
    fileName TEXT,
    filePath TEXT,
    timeStamp INTEGER NOT NULL
  )
`
  db.run(sql, err => {
    if (err) {
      console.error(err.message)
    } else {
      console.log('Table messages created successfully')
    }
  }).catch(err => {
    console.log(err)
  })
}

export const insert = insertData => {
  const sql = `INSERT INTO messages (chatId,msgFrom, msgTo, msgType, message, fileName, filePath, timeStamp) VALUES (?,?,?,?,?,?,?,?)`
  const values = [insertData.chatId, insertData.msgFrom, insertData.msgTo, insertData.msgType, insertData.message, insertData.fileName, insertData.filePath, insertData.timeStamp]
  db.run(sql, values, err => {
    if (err) {
      console.error(err.message)
    } else {
      console.log('Message inserted successfully')
    }
  }).catch(err => {
    console.log(err)
  })
}

export const get = (page, pageSize, chatId) => {
  const sql = `SELECT * FROM messages where chatId =${chatId} ORDER BY timeStamp DESC LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}`
  let oldDate = new Date().getTime()
  return new Promise((resolve, reject) => {
    db.all(sql, (err, rows) => {
      let newDate = new Date().getTime()
      console.log('插入耗时：' + (newDate - oldDate))
      if (err) {
        reject(err)
      } else {
        resolve(rows)
      }
    }).catch(err => {
      console.log(err)
    })
  })
}

export const search = searchText => {
  const sql = `SELECT * FROM messages where message like '%${searchText}%' ORDER BY timeStamp DESC`
  return new Promise((resolve, reject) => {
    db.all(sql, (err, rows) => {
      if (err) {
        reject(err)
      } else {
        resolve(rows)
      }
    }).catch(err => {
      console.log(err)
    })
  })
}
